﻿using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace SimpleORMNET
{
    public class SqlDatabaseReader
    {
        private string asyncConnectionString;
        private string connectionString;

        /// <summary>
        /// Connection string
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
            set
            {
                connectionString = value;
                asyncConnectionString = new SqlConnectionStringBuilder(connectionString) { AsynchronousProcessing = true }.ToString();
            }
        }

        /// <summary>
        /// Credentials
        /// </summary>
        public SqlCredential Credential { get; private set; }

        #region Constructors

        /// <summary>
        /// Creates new instance of DB reader with given connection string
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        public SqlDatabaseReader(string connectionString)
        {
            ConnectionString = connectionString;
        }

        /// <summary>
        /// Creates new instance of DB reader with given connection string and credentials
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        /// <param name="credential">Credentials</param>
        public SqlDatabaseReader(string connectionString, SqlCredential credential)
            : this(connectionString)
        {
            this.Credential = credential;
        }

        #endregion Constructors

        #region Private methods

        private IEnumerable<TResult> Get<TResult>(string sqlCommand, CommandType commandType, params SqlParameter[] parameters) where TResult : class, new()
        {
            using (var connection = GetOpenConnection(ConnectionString))
            {
                using (var command = new SqlCommand(sqlCommand, connection) { CommandType = commandType })
                {
                    command.Parameters.Clear();

                    foreach (var parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return reader.MapRow<TResult>();
                        }
                    }
                }
            }
        }

        private async Task<IEnumerable<TResult>> GetAsync<TResult>(string sqlQuery, CommandType commandType, params SqlParameter[] parameters) where TResult : class, new()
        {
            using (var connection = GetOpenConnection(asyncConnectionString))
            {
                using (var command = new SqlCommand(sqlQuery, connection) { CommandType = commandType })
                {
                    command.Parameters.Clear();
                    command.Parameters.AddRange(parameters);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        return await Task<IEnumerable<TResult>>.Run(() =>
                        {
                            return reader.GetMappedRows<TResult>().ToList();
                        });
                    }
                }
            }
        }

        private SqlConnection GetOpenConnection(string connectionString)
        {
            var connection = Credential == null ? (new SqlConnection(connectionString)) : (new SqlConnection(connectionString, Credential));
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            return connection;
        }

        #endregion Private methods

        #region Public methods

        /// <summary>
        /// Executes SQL query and returns result as an object with columns mapped to properties.
        /// </summary>
        /// <typeparam name="TResult">Type of object used to map results</typeparam>
        /// <param name="sqlQuery">SQL query to be executed</param>
        /// <param name="parameters">SQL query parameters</param>
        /// <returns>Object of specified type containing mapped results</returns>
        public IEnumerable<TResult> Get<TResult>(string sqlQuery, params SqlParameter[] parameters) where TResult : class, new()
        {
            return Get<TResult>(sqlQuery, CommandType.Text, parameters);
        }

        /// <summary>
        /// Executes SQL procedure and returns result as an object with columns mapped to properties.
        /// </summary>
        /// <typeparam name="TResult">Type of object used to map results</typeparam>
        /// <param name="sqlProcedure">SQL procedure to be executed</param>
        /// <param name="parameters">SQL procedure parameters</param>
        /// <returns>Object of specified type containing mapped results</returns>
        public IEnumerable<TResult> GetWithProcedure<TResult>(string sqlProcedure, params SqlParameter[] parameters) where TResult : class, new()
        {
            return Get<TResult>(sqlProcedure, CommandType.StoredProcedure, parameters);
        }

        public async Task<IEnumerable<TResult>> GetAsync<TResult>(string sqlQuery, params SqlParameter[] parameters) where TResult : class, new()
        {
            return await GetAsync<TResult>(sqlQuery, CommandType.Text, parameters);
        }

        public async Task<IEnumerable<TResult>> GetAsyncWithProcedure<TResult>(string sqlProcedure, params SqlParameter[] parameters) where TResult : class, new()
        {
            return await GetAsync<TResult>(sqlProcedure, CommandType.StoredProcedure, parameters);
        }

        /// <summary>
        /// Inserts record to specified table.
        /// </summary>
        /// <typeparam name="T">Type of object to map data from</typeparam>
        /// <param name="tableName">Name of table in database</param>
        /// <param name="@object">Object containing data to update</param>
        /// <returns>Number of rows affected</returns>
        public int Insert<T>(string tableName, T @object)
        {
            using (var connection = GetOpenConnection(ConnectionString))
            {
                var sqlCommand = @object.GetInsertCommand(tableName);
                using (var command = new SqlCommand(sqlCommand, connection) { CommandType = CommandType.Text })
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Inserts collection of records to specified table.
        /// </summary>
        /// <typeparam name="TCollection">Type of collection object</typeparam>
        /// <typeparam name="TItem">Type of item object</typeparam>
        /// <param name="tableName">Name of table in database</param>
        /// <param name="collection">Collection of objects containing data to insert</param>
        /// <returns>Number of rows affected</returns>
        public int Insert<TCollection, TItem>(string tableName, TCollection collection) where TCollection : IEnumerable<TItem>
        {
            int affectedRowsCount = 0;

            using (var connection = GetOpenConnection(ConnectionString))
            {
                foreach (var item in collection)
                {
                    var sqlCommand = item.GetInsertCommand(tableName);
                    using (var command = new SqlCommand(sqlCommand, connection) { CommandType = CommandType.Text })
                    {
                        affectedRowsCount += command.ExecuteNonQuery();
                    }
                }
            }
            return affectedRowsCount;
        }

        /// <summary>
        /// Updates record in specified table based on primary key property value
        /// </summary>
        /// <typeparam name="T">Type of object to map data from</typeparam>
        /// <param name="tableName">Name of table in database</param>
        /// <param name="@object">Object containing data to update</param>
        /// <returns>Number of rows affected</returns>
        public int Update<T>(string tableName, T @object)
        {
            using (var connection = GetOpenConnection(ConnectionString))
            {
                var sqlCommand = @object.GetUpdateCommand(tableName);
                using (var command = new SqlCommand(sqlCommand, connection) { CommandType = CommandType.Text })
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Updates collection of records in specified table based on primary key property value
        /// </summary>
        /// <typeparam name="TCollection">Type of collection object</typeparam>
        /// <typeparam name="TItem">Type of item object</typeparam>
        /// <param name="tableName">Name of table in database</param>
        /// <param name="collection">Collection of objects containing data to update</param>
        /// <returns>Number of rows affected</returns>
        public int Update<TCollection, TItem>(string tableName, TCollection collection) where TCollection : IEnumerable<TItem>
        {
            int updatedRowsCount = 0;

            using (var connection = GetOpenConnection(ConnectionString))
            {
                foreach (var item in collection)
                {
                    var sqlCommand = item.GetUpdateCommand(tableName);
                    using (var command = new SqlCommand(sqlCommand, connection) { CommandType = CommandType.Text })
                    {
                        updatedRowsCount += command.ExecuteNonQuery();
                    }
                }
            }
            return updatedRowsCount;
        }

        /// <summary>
        /// Deletes record from specified table based on primary key property value
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">Name of table in database</param>
        /// <param name="@object">Object containing data to delete</param>
        /// <returns>Number of rows affected</returns>
        public int Delete<T>(string tableName, T @object)
        {
            using (var connection = GetOpenConnection(ConnectionString))
            {
                var sqlCommand = @object.GetDeleteCommand(tableName);
                using (var command = new SqlCommand(sqlCommand, connection) { CommandType = CommandType.Text })
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

        #endregion Public methods
    }
}